﻿using System;
using System.Configuration;
using System.Data;
using System.Data.Common;
using com.wei8808.utility;

namespace com.wei8808.dao
{
    public class DbHelper
    {
        private static readonly string DbProviderName = ConfigurationManager.ConnectionStrings["ConnectionString"].ProviderName;
        private static readonly string DbConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        private readonly DbConnection _connection;
        public DbHelper()
        {
            try
            {
                _connection = CreateConnection(DbConnectionString);
            }
            catch (Exception ex)
            {
                EventsLog.WriteLog("ErrorInfo" + ex.Message);
            }

        }
        public DbHelper(string connectionString)
        {
            try
            {
                _connection = CreateConnection(connectionString);
            }
            catch (Exception ex)
            {
                EventsLog.WriteLog("ErrorInfo" + ex.Message);
            }

        }
        public static DbConnection CreateConnection()
        {
            var dbfactory = DbProviderFactories.GetFactory(DbProviderName);
            var dbconn = dbfactory.CreateConnection();
            if (dbconn != null)
            {
                dbconn.ConnectionString = DbConnectionString;

            }
            return dbconn;
        }
        public static DbConnection CreateConnection(string connectionString)
        {
            var dbfactory = DbProviderFactories.GetFactory(DbProviderName);
            var dbconn = dbfactory.CreateConnection();
            if (dbconn != null)
            {
                dbconn.ConnectionString = connectionString;

            }
            return dbconn;
        }
        public DbConnection GetConnection()
        {
            return _connection;
        }
        public DbCommand GetStoredProcCommond(string storedProcedure)
        {
            var dbCommand = _connection.CreateCommand();
            dbCommand.CommandText = storedProcedure;
            dbCommand.CommandType = CommandType.StoredProcedure;
            return dbCommand;
        }
        public DbCommand GetSqlStringCommond(string sqlQuery)
        {
            var dbCommand = _connection.CreateCommand();
            dbCommand.CommandText = sqlQuery;
            dbCommand.CommandType = CommandType.Text;
            return dbCommand;
        }
        //增加参数#region 增加参数
        #region
        public void AddParameterCollection(DbCommand cmd, DbParameterCollection dbParameterCollection)
        {
            foreach (DbParameter dbParameter in dbParameterCollection)
            {
                cmd.Parameters.Add(dbParameter);
            }
        }

        public void AddOutParameter(DbCommand cmd, string parameterName, DbType dbType, int size)
        {
            var dbParameter = cmd.CreateParameter();
            dbParameter.DbType = dbType;
            dbParameter.ParameterName = parameterName;
            dbParameter.Size = size;
            dbParameter.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(dbParameter);
        }

        public void AddOutParameter(DbCommand cmd, string parameterName, DbType dbType)
        {
            var dbParameter = cmd.CreateParameter();
            dbParameter.DbType = dbType;
            dbParameter.ParameterName = parameterName;
            dbParameter.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(dbParameter);
        }

        public void AddInParameter(DbCommand cmd, string parameterName, DbType dbType, object value)
        {
            var dbParameter = cmd.CreateParameter();
            dbParameter.DbType = dbType;
            dbParameter.ParameterName = parameterName;
            dbParameter.Value = value;
            dbParameter.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(dbParameter);
        }

        public void AddInParameter<T>(DbCommand cmd, string parameterName, T value)
        {
            var dbParameter = cmd.CreateParameter();
            dbParameter.DbType = DbTypeConvert.TypeToDbType(typeof(T));
            dbParameter.ParameterName = parameterName;
            dbParameter.Value = value;
            dbParameter.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(dbParameter);
        }

        public void AddInParameter(DbCommand cmd, string parameterName, DbType dbType)
        {
            var dbParameter = cmd.CreateParameter();
            dbParameter.DbType = dbType;
            dbParameter.ParameterName = parameterName;
            dbParameter.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(dbParameter);
        }

        public void AddReturnParameter(DbCommand cmd, string parameterName, DbType dbType)
        {
            var dbParameter = cmd.CreateParameter();
            dbParameter.DbType = dbType;
            dbParameter.ParameterName = parameterName;
            dbParameter.Direction = ParameterDirection.ReturnValue;
            cmd.Parameters.Add(dbParameter);
        }

        public DbParameter GetParameter(DbCommand cmd, string parameterName)
        {
            return cmd.Parameters[parameterName];
        }

        #endregion

        //执行#region 执行
        #region

        public DataSet ExecuteDataSet(DbCommand cmd)
        {
            var dbfactory = DbProviderFactories.GetFactory(DbProviderName);
            var dbDataAdapter = dbfactory.CreateDataAdapter();
            if (dbDataAdapter != null) dbDataAdapter.SelectCommand = cmd;
            var ds = new DataSet();
            if (dbDataAdapter != null) dbDataAdapter.Fill(ds);
            return ds;
        }

        public DataTable ExecuteDataTable(DbCommand cmd)
        {
            var dbfactory = DbProviderFactories.GetFactory(DbProviderName);
            var dbDataAdapter = dbfactory.CreateDataAdapter();
            if (dbDataAdapter != null) dbDataAdapter.SelectCommand = cmd;
            var dataTable = new DataTable();
            if (dbDataAdapter != null) dbDataAdapter.Fill(dataTable);
            return dataTable;
        }

        public DbDataReader ExecuteReader(DbCommand cmd)
        {
            DbDataReader reader = null;
            try
            {
                cmd.Connection.Open();
                reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

            }
            catch (Exception ex)
            {
                EventsLog.WriteLog("ErrorInfo" + ex.Message);
            }
            return reader;
        }

        public int ExecuteNonQuery(DbCommand cmd)
        {
            var ret = 0;
            try
            {
                cmd.Connection.Open();
                ret = cmd.ExecuteNonQuery();
                cmd.Connection.Close();

            }
            catch (Exception ex)
            {
                EventsLog.WriteLog("ErrorInfo" + ex.Message);
            }
            return ret;
        }

        public object ExecuteScalar(DbCommand cmd)
        {
            object ret = null;
            try
            {
                cmd.Connection.Open();
                ret = cmd.ExecuteScalar();
                cmd.Connection.Close();
            }
            catch (Exception ex)
            {
                EventsLog.WriteLog("ErrorInfo" + ex.Message);
            }

            return ret;
        }

        #endregion

        //执行事务#region 执行事务
        #region
        public DataSet ExecuteDataSet(DbCommand cmd, Trans t)
        {
            cmd.Connection = t.DbConnection;
            cmd.Transaction = t.DbTrans;
            var dbfactory = DbProviderFactories.GetFactory(DbProviderName);
            var dbDataAdapter = dbfactory.CreateDataAdapter();
            var ds = new DataSet();
            if (dbDataAdapter != null)
            {
                dbDataAdapter.SelectCommand = cmd;
                dbDataAdapter.Fill(ds);
            }
            return ds;
        }

        public DataTable ExecuteDataTable(DbCommand cmd, Trans t)
        {
            cmd.Connection = t.DbConnection;
            cmd.Transaction = t.DbTrans;
            var dbfactory = DbProviderFactories.GetFactory(DbProviderName);
            var dbDataAdapter = dbfactory.CreateDataAdapter();
            var dataTable = new DataTable();
            if (dbDataAdapter != null)
            {
                dbDataAdapter.SelectCommand = cmd;
                dbDataAdapter.Fill(dataTable);
            } return dataTable;

        }

        public DbDataReader ExecuteReader(DbCommand cmd, Trans t)
        {
            cmd.Connection.Close();
            cmd.Connection = t.DbConnection;
            cmd.Transaction = t.DbTrans;
            var reader = cmd.ExecuteReader();
            return reader;
        }

        public int ExecuteNonQuery(DbCommand cmd, Trans t)
        {
            cmd.Connection.Close();
            cmd.Connection = t.DbConnection;
            cmd.Transaction = t.DbTrans;
            int ret = cmd.ExecuteNonQuery();
            return ret;
        }

        public object ExecuteScalar(DbCommand cmd, Trans t)
        {
            cmd.Connection.Close();
            cmd.Connection = t.DbConnection;
            cmd.Transaction = t.DbTrans;
            object ret = cmd.ExecuteScalar();
            return ret;
        }

        #endregion
    }
    public class Trans : IDisposable
    {
        private readonly DbConnection _conn;
        private readonly DbTransaction _dbTrans;
        public DbConnection DbConnection
        {
            get { return _conn; }
        }
        public DbTransaction DbTrans
        {
            get { return _dbTrans; }
        }
        public Trans()
        {
            _conn = DbHelper.CreateConnection();
            _conn.Open();
            _dbTrans = _conn.BeginTransaction();
        }
        public Trans(string connectionString)
        {
            _conn = DbHelper.CreateConnection(connectionString);
            _conn.Open();
            _dbTrans = _conn.BeginTransaction();
        }
        public void Commit()
        {
            _dbTrans.Commit();
            Colse();
        }
        public void RollBack()
        {
            _dbTrans.Rollback();
            Colse();
        }
        public void Dispose()
        {
            Colse();
        }
        public void Colse()
        {
            if (_conn.State == ConnectionState.Open)
            {
                _conn.Close();
            }
        }
    }
}
